package com.scalar.forecast.columnDisplay.dao;

import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.forecast.utils.ForecastSqlQueryMappings;
import com.scalar.forecast.columnDisplay.ColumnMetaData;
import com.scalar.ScalarException;

import java.util.ArrayList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;


/**
 * Data Access Class for Meta Data
 *
 * Created by Veeru. 
 * Date: Feb 16, 2005
 * Time: 10:32:31 AM 
 */

public class SearchMetaDataDAO {

    /*
     *  Loads the Meta Data related to the table name passed
     */
    public static ArrayList loadMetaData( DBTransaction dbTransaction, String tableName ) throws ScalarException {

        Connection        conn              = null;
        PreparedStatement pstmt             = null;
        ResultSet         rs                = null;
        boolean           success           = false;
        ArrayList         metaDataList = new ArrayList();

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get("forecast.columnDisplay.loadMetaData" ) );
            pstmt.setString( 1, tableName );

            rs = pstmt.executeQuery();
            ColumnMetaData columnMetaData = null;
            while( rs.next() )
            {
                columnMetaData = new ColumnMetaData();
                columnMetaData.setRowId( rs.getInt ( "RowId" ) );
                columnMetaData.setColumnName( rs.getString ( "COLNAME" ) );
                columnMetaData.setDisplayName( rs.getString ( "COLDISPLAY" ) );
                columnMetaData.setOrder( rs.getInt("SORTORDER") );
                //metaDataList.add( new ColumnMetaData( rs.getString("COLNAME"), rs.getString("COLDISPLAY"), rs.getInt("SORTORDER") ) );
                metaDataList.add ( columnMetaData );

            }
            success = true;

        } catch ( Exception e ) {
            e.printStackTrace();
            throw new ScalarException ( "Could not load Meta Data Products from database");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
        return metaDataList;
    }

    /*
     *  Saves the Meta Data
     */
   /* public static void saveMetaData( DBTransaction dbTransaction, String tableName, ArrayList metaDataList ) throws ScalarException {

        Connection        conn              = null;
        PreparedStatement pstmt             = null;
        ResultSet         rs                = null;
        boolean           success           = false;

        try {
            if ( dbTransaction == null ) {
                conn = DataSource.getConnection();
            }else {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( ForecastSqlQueryMappings.get("forecast.columnDisplay.saveMetaData") );
            for (int i = 0; i < metaDataList.size(); i++) {
                ColumnMetaData columnMetaData = (ColumnMetaData) metaDataList.get( i );
                pstmt.setString( 1, columnMetaData.getDisplayName() );
                pstmt.setInt( 2, columnMetaData.getOrder() );
                pstmt.setString( 3, columnMetaData.getColumnName() );
                pstmt.setString( 4, tableName);
                pstmt.addBatch();
            }
            pstmt.executeBatch();
        } catch ( Exception e ) {
            e.printStackTrace();
            throw new ScalarException ( "Could not save Products");
        }
        finally
        {
            if (dbTransaction == null)
            {
                 try
                 {
                     if (conn != null)
                     {
                         if (success == true)
                             conn.commit();
                         else
                             conn.rollback();
                     }
                 }
                 catch (SQLException ignore) {}
             }
             if ( rs != null )
             {
                 try { rs.close(); } catch ( SQLException ignore ) { }
             }
             if (pstmt != null)
             {
                 try { pstmt.close(); } catch (SQLException ignore){}
             }
             if (conn != null)
             {
                 try { conn.close(); } catch (SQLException ignore){}
             }
        }
    } */

    /**
     * Used to know whether the user has any entries in editColumn display table.
     * @param tableName
     * @param userId
     * @return
     */
    public static boolean isUserHasEntries ( String tableName, String userId )
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean hasEntry = false;
        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.columnDisplay.isUserHasEntries" ) );
            pstmt.setString ( 1, tableName );
            pstmt.setString ( 2, userId );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                int count = rset.getInt ( 1 );
                if ( count > 0 )
                {
                    hasEntry = true;
                }
                else
                {
                    hasEntry = false;
                }
            }
        }
        catch ( Exception e )
        {
            System.out.println ( "--Exception in method isUserHasEntries---" );
            e.printStackTrace();
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return hasEntry;
    }

    /**
     * Loads the column metaData Info with respect to the userId passed.
     * @param dbTransaction
     * @param tableName
     * @param userId
     * @return
     * @throws ScalarException
     */
    public static ArrayList loadMetaData( DBTransaction dbTransaction,String tableName,String userId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        boolean success = false;
        ArrayList metaDataList = new ArrayList();
        ColumnMetaData columnMetaData = null;
        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            if ( userId == null )
            {
                pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.columnDisplay.loadMetaData.user.null" ) );
            }
            else
            {
                pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.columnDisplay.loadMetaData.user" ) );
                pstmt.setString ( 2, userId );
            }
            pstmt.setString ( 1, tableName );
            rset = pstmt.executeQuery();
            while ( rset.next() )
            {
                columnMetaData = new ColumnMetaData( );

                columnMetaData.setTableName( tableName );
                columnMetaData.setRowId( rset.getInt ( "ROWID" ) );
                columnMetaData.setColumnName( rset.getString("COLNAME") );
                columnMetaData.setDisplayName(rset.getString("COLDISPLAY") );
                columnMetaData.setOrder( rset.getInt("SORTORDER") );
                columnMetaData.setUserId ( rset.getString( "USERID") );
                //columnMetaData.setDisplay( rset.getInt ( "" ) == 0 ? true : false ); //todo check...
                metaDataList.add ( columnMetaData );
            }
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "---Exception while loading the column meta data----" );
            e.printStackTrace();
            throw new ScalarException( "Could Not Load Column Meta Data List");
        }
        finally
        {
            if ( dbTransaction == null )
            {
               try
                 {
                     if (conn != null)
                     {
                         if ( success )
                         {
                             conn.commit();
                         }
                         else
                         {
                            conn.rollback();
                         }
                     }
                 }
                 catch ( SQLException ignore ) {}
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException e ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException e ){ }
            }
            if ( conn != null )
            {
                try { conn.close();} catch( SQLException e ){ }
            }
        }
        return metaDataList;
    }

    /**
     * Saves the edit column info for the tableName, userId passed.<br>
     * First It extracts the editColumn Info from table for tableName passed and userId is Null. Then these records will be inserted for the userId passed to this method.
     *
     * @param dbTransaction
     * @param tableName
     * @param userId
     * @throws ScalarException
     */
    public static void saveEntriesWithUserId ( DBTransaction dbTransaction, String tableName, String userId ) throws ScalarException
    {
        ArrayList metaDataList = null;
        try
        {
            //getting basic edit column fields..
            metaDataList = loadMetaData( dbTransaction, tableName, null );
            //now save these fields with userId..
            insertMetaData ( dbTransaction,metaDataList,userId);
        }
        catch ( Exception  e)
        {
            System.out.println ( "--Exception in saving entries with userId--" );
            e.printStackTrace();
            throw new ScalarException( "Could Not save edit column entries--");
        }
    }

    public static void insertMetaData ( DBTransaction dbTransaction, ArrayList metaDataList, String userId ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ColumnMetaData columnMetaData = null;
        boolean success = false;
        try
        {
            int metaDataListSize = metaDataList.size();
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.columnDisplay.insertMetaData" ) );
            for ( int i=0; i < metaDataListSize; i++ )
            {
                columnMetaData = ( ColumnMetaData ) metaDataList.get( i );
                pstmt.setString ( 1, columnMetaData.getTableName() );
                pstmt.setString ( 2, columnMetaData.getColumnName() );
                pstmt.setString ( 3, columnMetaData.getDisplayName() );
                pstmt.setInt ( 4, columnMetaData.getOrder() );
                pstmt.setString ( 5, userId );
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "--Exception while inserting column meta data list--");
            e.printStackTrace();
            throw new ScalarException ( "Could not Insert column meta data list");
        }
        finally
        {
            if ( dbTransaction == null )
            {
               try
                 {
                     if (conn != null)
                     {
                         if ( success )
                         {
                             conn.commit();
                         }
                         else
                         {
                            conn.rollback();
                         }
                     }
                 }
                 catch ( SQLException ignore ) { }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
    }

    /**
     * Updates the metaData
     * @param dbTransaction
     * @param metaDataList
     * @throws ScalarException
     */
    public static void saveMetaData ( DBTransaction dbTransaction, ArrayList metaDataList ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ColumnMetaData columnMetaData = null;
        boolean success = false;
        try
        {
            int metaDataListSize = metaDataList.size();
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement ( ForecastSqlQueryMappings.get ( "forecast.columnDisplay.saveMetaData" ) );
            for ( int i=0; i < metaDataListSize; i++ )
            {
                columnMetaData = ( ColumnMetaData ) metaDataList.get( i );
                pstmt.setString ( 1, columnMetaData.getDisplayName() );
                pstmt.setInt ( 2, columnMetaData.getOrder() );
                pstmt.setInt ( 3, columnMetaData.getRowId () );
                pstmt.addBatch();
            }
            pstmt.executeBatch();
            success = true;
        }
        catch ( Exception e )
        {
            System.out.println ( "--Exception while saving column meta data list--");
            e.printStackTrace();
            throw new ScalarException ( "Could not save column meta data list");
        }
        finally
        {
            if ( dbTransaction == null )
            {
               try
                 {
                     if (conn != null)
                     {
                         if ( success )
                         {
                             conn.commit();
                         }
                         else
                         {
                            conn.rollback();
                         }
                     }
                 }
                 catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) { }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
    }

}
